package com.hectopascal.goalance.repository;

import com.hectopascal.goalance.pojo.User;
import com.hectopascal.goalance.pojo.dto.UserBack;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Optional;

@Repository  //将当前类注册为spring bean
public interface UserRepository extends CrudRepository<User, Integer> {
    @Query(value = "SELECT * FROM user WHERE username = :username", nativeQuery = true)
    Optional<User> findByUsername(@Param("username") String username);

    @Modifying
    @Transactional
    @Query(value = "UPDATE user SET "
            + "username = CASE WHEN :username IS NOT NULL THEN :username ELSE username END, "
            + "password = CASE WHEN :password IS NOT NULL THEN :password ELSE password END, "
            + "role = CASE WHEN :role IS NOT NULL THEN :role ELSE role END, "
            + "department_id = CASE WHEN :department_id IS NOT NULL THEN :department_id ELSE department_id END "
            + "WHERE id = :id",
            nativeQuery = true)
    int updateUser(@Param("username") String username,
                   @Param("password") String password,
                   @Param("role") Integer role,
                   @Param("department_id") Integer departmentId,
                   @Param("id") Integer id);

    @Modifying
    @Transactional
    @Query(value = "DELETE FROM user WHERE id = :id", nativeQuery = true)
    int deleteUserById(@Param("id") Integer id);

    @Query(value = "SELECT u.*, dep.name AS departmentname " +
            "FROM user u " +
            "LEFT JOIN department dep ON u.department_id = dep.id " +
            "WHERE u.id = :id", nativeQuery = true)
    Optional<UserBack> findUserById(@Param("id") Integer id);

    @Query(value = "SELECT u.*, dep.name AS departmentname " +
            "FROM user u " +
            "LEFT JOIN department dep ON u.department_id = dep.id " +
            "WHERE role = :role", nativeQuery = true)
    List<UserBack> findByRole(@Param("role") String role);

    @Modifying
    @Transactional
    @Query(value = "UPDATE user SET avatar = :avatar WHERE id = :id", nativeQuery = true)
    int updateAvatar(@Param("avatar") String avatar, @Param("id") Integer id);

    @Query(value = "SELECT u.*, dep.name AS departmentname " +
            "FROM user u " +
            "LEFT JOIN department dep ON u.department_id = dep.id " +
            "WHERE role = :role AND u.department_id = :departmentId", nativeQuery = true)
    List<UserBack> findByRoleAndDep(@Param("role") String role,@Param("departmentId") Integer departmentId);

    @Query(value = "SELECT department_id FROM user WHERE id = :id", nativeQuery = true)
    Integer getDepartmentId(@Param("id") Integer id);
}
